import traceback
import pyautogui
import os
import time
import pyperclip as clip
import json
import pandas as pd
from datetime import datetime
from sqlalchemy import create_engine
import pypinyin
from bs4 import BeautifulSoup
import random
from jsjj.util.configUtil import *
from jsjj.util.dbUtil import *
from jsjj.util.emailUtil import *
from jsjj.util.timeUtil import *
from jsjj.util.logUtil import *

# 运行模式: 日模式；周模式；月模式
runMode = '日模式'      #抓的是月数据
modeName = '拼多多_电霸_近30天热销商品'


# 左边点击，直到点击到一级类目，该过程不参与循环
def UntilCat1():
    # 关闭电霸每天晚上会弹出的一个屏蔽页面
    pyautogui.FAILSAFE = False
    sleep(6)
    pyautogui.moveTo(1413, 385)
    # pyautogui.scroll(5000)
    pyautogui.click()

    sleep(4)
    # 向上翻到顶叶
    pyautogui.scroll(5000)

    # 顶部行业菜单
    pyautogui.moveTo(313, 190, 6)
    pyautogui.click()

    # 拼多多-电霸-左侧菜单-热销商品
    pyautogui.moveTo(127, 873, 2)
    pyautogui.click()

    sleep(10)

    # 拼多多-电霸-中间左上角主营类目
    pyautogui.moveTo(352, 250, 8)
    pyautogui.click()

    # 焦点切换到输入框
    sleep(2)
    pyautogui.keyDown('tab')
    pyautogui.keyUp('tab')

    sleep(2)

    clip.copy('家纺家具家装')  # 先复制
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴
    # pyautogui.typewrite(pinyin('家纺家具家装') + "1")

    pyautogui.moveTo(352, 280, 2)
    pyautogui.click()

    pyautogui.moveTo(333, 327, 4)
    pyautogui.click()

    sleep(6)

    # 拼多多-电霸-中间-一级类目后台下拉菜单
    pyautogui.moveTo(524, 296, 8)
    pyautogui.click()

    sleep(2)
    # pyautogui.typewrite(pinyin('住宅家具') + "1")
    clip.copy('住宅家具')  # 先复制
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴

    sleep(2)
    pyautogui.keyDown('enter')
    pyautogui.keyUp('enter')
    return


def LoopCat2_3(cat1MenuName, cat2MenuName, cat3MenuName, cat1, cat2, cat3, conn):
    # 一级也是需要的
    pyautogui.moveTo(538, 298, 2)
    pyautogui.click()

    # 成套家具
    sleep(2)
    # pyautogui.typewrite(cat1MenuName)
    clip.copy(cat1MenuName)  # 先复制
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴

    sleep(2)
    pyautogui.keyDown('enter')
    pyautogui.keyUp('enter')

    # 拼多多-电霸-中间-二级类目后台下拉菜单 cat2MenuName
    pyautogui.moveTo(904, 296, 2)
    pyautogui.click()

    # 成套家具
    sleep(2)
    # pyautogui.typewrite(cat2MenuName)
    clip.copy(cat2MenuName)  # 先复制
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴

    sleep(2)
    pyautogui.keyDown('enter')
    pyautogui.keyUp('enter')

    # cat3MenuName
    pyautogui.moveTo(1310, 298, 2)
    pyautogui.click()

    # 成套家具
    sleep(2)
    # pyautogui.typewrite(cat3MenuName)
    clip.copy(cat3MenuName)  # 先复制
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴

    sleep(2)
    pyautogui.keyDown('enter')
    pyautogui.keyUp('enter')

    # 搜索（必须点击，否则页面查询条件记不住）
    pyautogui.moveTo(1513, 296, 2)
    pyautogui.click()

    # 要点击到7前的热销商品(先F12）
    sleep(20)
    pyautogui.keyDown('f12')
    pyautogui.keyUp('f12')

    # 第二个tab页标签，30天(change)
    pyautogui.moveTo(619, 387, 6)
    pyautogui.click()
    sleep(20)

    # F12过滤函数名称输入框
    pyautogui.moveTo(1587, 166, 12)  # 1347  -> 1587   240
    pyautogui.doubleClick()

    # 英文输入
    sleep(2)
    # pyautogui.typewrite("hotSale")
    clip.copy('hotSale?request')  # 先复制
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴

    pyautogui.keyDown('enter')
    pyautogui.keyUp('enter')

    # 拼多多-电霸-F12后的Name区域选择
    pyautogui.moveTo(1538, 320, 4)
    pyautogui.click()

    # 拼多多-电霸-F12后的Response区域选择
    pyautogui.moveTo(1838, 490, 2)
    pyautogui.click()

    sleep(2)
    pyautogui.hotkey('ctrl', 'a')

    sleep(2)
    pyautogui.hotkey('ctrl', 'c')

    # 读取剪切板内容
    sleep(2)
    value = clip.paste()
    # if (value.find('hotSale')) == -1:
    #     return
    value = '<html><head><title>fix html part</title></head><body>' + value
    value = value + '</body></html>'
    try:
        htmlFromWeb = BeautifulSoup(value)
    except Exception as e:
        print('发生了异常(没查询到数据)，不过我自己跳过了:' + str(e))
        return
    jsonHtml = {'goodsName': [], 'goodsId': [], 'sales1': [], 'sales7': [], 'sales30': [], 'increment7': [],
                'groupBuyPrice': [], 'picUrl': []}
    list = htmlFromWeb.findAll('tr')
    for obj in list:
        jsonHtml['goodsName'].append(obj.select('td')[1].text.split('\n')[1].replace('\r', ''))
        jsonHtml['goodsId'].append(obj.select('td')[1].text.split("商品ID:")[1].split('\n')[0].replace('\r', ''))
        jsonHtml['sales1'].append(obj.select('td')[2].text)
        jsonHtml['sales7'].append(obj.select('td')[3].text)
        jsonHtml['sales30'].append(obj.select('td')[4].text)
        jsonHtml['increment7'].append(obj.select('td')[5].text)
        jsonHtml['groupBuyPrice'].append(obj.select('td')[6].text)
        jsonHtml['picUrl'].append(obj.select('td')[0].select("img")[0].attrs['src'])
    df = pd.DataFrame(jsonHtml)
    df.head()
    df['statDate'] = datetime.datetime.now()
    # 住宅家具
    df['cat1'] = cat1
    # 成套家具
    df['cat2'] = cat2
    df['cat3'] = cat3

    df['运行模式'] = runMode
    df['统计月'] = ''
    df['统计周'] = ''
    df['统计日'] = ''
    df['统计日周月'] = ''
    if runMode == '月模式':
        df['统计月'] = str(datetime.datetime.now().month - 1) + "月"
        df['统计日周月'] = df['统计月']
    if runMode == '周模式':
        df['统计周'] = getLastWeekOfYear(datetime.datetime.now().year, datetime.datetime.now().month,datetime.datetime.now().day)
        df['统计日周月'] = df['统计周']
    if runMode == '日模式':
        df['统计日'] = (datetime.datetime.now() + datetime.timedelta(days=-1)).strftime(
            "%Y-%m-%d %H:%M:%S")  # 减一天，统计的是昨天的数据
        df['统计日周月'] = df['统计日']
    df['插入时间'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

    # 解决可能出现的超时问题bugfix 2020.08.18
    conn.connection.connection.ping(reconnect=True)
    # 拼多多_电霸_近30天热销商品
    df.to_sql(name='拼多多_电霸_近30天热销商品', con=conn, if_exists='append', index=False)
    # print("执行了一次保存:" + cat2MenuName + ',录入了:' + str(len(jsonFromWeb['series'][0]['data'])) + '条数据')

    sleep(4)
    pyautogui.keyDown('f12')
    pyautogui.keyUp('f12')
    sleep(4)

    return


def pinyin(word):
    s = ''
    for i in pypinyin.pinyin(word, style=pypinyin.NORMAL):
        s += ''.join(i)
    return s


def executeCatchWeb(engine, conn):
    try:
        LogTaskAndMachine('拼多多_电霸_近30天热销商品', engine, conn, '', runMode)
        UntilCat1()

        df = pd.read_sql(sql="select cat1,cat2,cat3,channel,dianba_rxsp30 from 平台_类目表 t where t.排除 is null order by priority asc", con=engine)
        dfCat = df[(df['channel'] == '拼多多') & (df['dianba_rxsp30'] == '0')][
            ['cat1', 'cat2', 'cat3']].drop_duplicates()
        for index, row in dfCat.iterrows():
            # LoopCat2_3(pinyin(row['cat2']) + '1', pinyin(row['cat3']) + '1', row['cat1'], row['cat2'], row['cat3'], conn)
            LoopCat2_3(row['cat1'], row['cat2'], row['cat3'], row['cat1'], row['cat2'], row['cat3'], conn)
            # 完成一个更新一个
            engine.execute(" update 平台_类目表 set dianba_rxsp30='1' where cat1='" + row['cat1'] + "' and cat2='" + row[
                'cat2'] + "' and cat3='" + row['cat3'] + "' and channel='拼多多' ")

            # 避免客户端IE操作过于频繁
            # sleep(random.randint(1, 2) * 60)
            sleep(100)    #8.1日计算出来的120,100

    except Exception as e:
        traceback.print_exc()
        sendAlert('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1], engine, conn)
        print('【异常中断】' + modeName, '异常:' + str(e) + '|报错文件:' + os.path.split(__file__)[-1])
        return
    sendFinalSuccessEmail('●正常完成●' + modeName, '', engine, conn, modeName, runMode)


if __name__ == '__main__':
    engine, conn = getConn()
    executeCatchWeb(engine, conn)
